﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using MDL;

namespace DAL
{
    public class DAL_Products
    {
        /// <summary>
        /// 根据产品类型获取产品信息
        /// </summary>
        /// <param name="Ptid">产品类型ID</param>
        /// <returns>产品信息</returns>
        public static List<Products> GetProducts(int Ptid)
        {
            string sql = "select * from Products where 1=1 ";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (Ptid != 0)
            {
                sql += " and PTID = @PTID";
                sprs.Add(new SqlParameter("@PTID", Ptid));
            }

            List<Products> list = new List<Products>();
            using (SqlDataReader sdr = DBHelper.白道(sql, sprs.ToArray(), CommandType.Text))
            {
                while (sdr.Read())
                {
                    Products pt = new Products();
                    pt.ProductID = Convert.ToInt32(sdr[0]);
                    pt.ProductName = sdr[1].ToString();
                    pt.PTID = Convert.ToInt32(sdr[2]);
                    pt.ProductJP = sdr[3].ToString();
                    pt.ProductPrice = Convert.ToDouble(sdr[4]);
                    list.Add(pt);
                }
            }
            return list;
        }
        /// <summary>
        /// 根据简拼获取商品信息
        /// </summary>
        /// <param name="ProductJP">简拼</param>
        /// <returns>商品信息</returns>
        public static List<Products> GetProducts(string ProductJP)
        {
            string sql = "select * from Products p inner join ProductType t on p.PTID = t.PTID where 1=1 ";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (!ProductJP.Equals(""))
            {
                sql += " and ProductJP like @ProductJP";
                sprs.Add(new SqlParameter("@ProductJP", "%" + ProductJP + "%"));
            }

            List<Products> list = new List<Products>();
            using (SqlDataReader sdr = DBHelper.白道(sql, sprs.ToArray(), CommandType.Text))
            {
                while (sdr.Read())
                {
                    Products pt = new Products();
                    pt.ProductID = Convert.ToInt32(sdr[0]);
                    pt.ProductName = sdr[1].ToString();
                    pt.PTID = Convert.ToInt32(sdr[2]);
                    pt.ProductJP = sdr[3].ToString();
                    pt.ProductPrice = Convert.ToDouble(sdr[4]);
                    pt.PTName = sdr[6].ToString();
                    list.Add(pt);
                }
            }
            return list;
        }
        /// <summary>
        /// 查询商品信息
        /// </summary>
        /// <param name="ID">根据类型ID</param>
        /// <returns>商品信息</returns>
        public static List<Products> GetProduct(int ID)
        { 
            string sql = @"select p.ProductName,t.PTName,p.ProductPrice from Products p 
                            inner join ProductType t on p.PTID=t.PTID where 1=1 ";
            List<SqlParameter> sprs = new List<SqlParameter>();
            if (ID > 0)
            {
                sql += " and t.PTID=@ID";
                sprs.Add(new SqlParameter("@ID", ID));
            }
            List<Products> list = new List<Products>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs.ToArray(), CommandType.Text))
            {
                while (dr.Read())
                {
                    Products p = new Products();
                    p.ProductName = dr[0].ToString();
                    p.PTName = dr[1].ToString();
                    p.ProductPrice = Convert.ToInt32(dr[2]);
                    list.Add(p);
                }
            }
            return list;
        }
        /// <summary>
        /// 查询是否已有相同的商品信息名称
        /// </summary>
        /// <param name="name">要查询的商品信息名称</param>
        /// <returns>是否有</returns>
        public static List<Products> GetProductName(string name)
        {
            string sql = "select * from Products where ProductName = @name";
            SqlParameter[] sprs = { new SqlParameter("@name",name)};
            List<Products> list = new List<Products>();
            using (SqlDataReader dr = DBHelper.白道(sql, sprs, CommandType.Text))
            {
                while (dr.Read())
                {
                    Products p = new Products();
                    p.ProductName = dr[1].ToString();
                    p.ProductJP = dr[3].ToString();
                    list.Add(p);
                }
            }
            return list;
        }
        /// <summary>
        /// 增加商品信息
        /// </summary>
        /// <param name="name">商品名称</param>
        /// <param name="ID">商品所属的类型</param>
        /// <param name="Jp">商品简拼</param>
        /// <param name="price">商品价格</param>
        /// <returns>受影响的行数</returns>
        public static int AddProducts(string name,int ID,string Jp,int price)
        {
            string sql = "insert into Products values(@name,@ID,@Jp,@price)";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",name),
                                  new SqlParameter("@ID",ID),
                                  new SqlParameter("@Jp",Jp),
                                  new SqlParameter("@price",price)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 修改商品信息
        /// </summary>
        /// <param name="name">修改商品名称</param>
        /// <param name="ID">商品所属的类型</param>
        /// <param name="Jp">商品简拼</param>
        /// <param name="price">商品价格</param>
        /// <param name="name1">目标商品名称</param>
        /// <returns>受影响的行数</returns>
        public static int UpdateProducts(string name, int ID, string Jp, int price,string name1)
        {
            string sql = @"update Products set ProductName=@name,PTID = @ID,ProductJP = @jp,
                        ProductPrice = @price where ProductName = @name1";
            SqlParameter[] sprs = { 
                                  new SqlParameter("@name",name),
                                  new SqlParameter("@ID",ID),
                                  new SqlParameter("@Jp",Jp),
                                  new SqlParameter("@price",price),
                                  new SqlParameter("@name1",name1)
                                  };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
        /// <summary>
        /// 删除商品信息
        /// </summary>
        /// <param name="name">根据商品名称</param>
        /// <returns>受影响的行数</returns>
        public static int DeleteProducts(string name)
        {
            string sql = "delete from Products where ProductName = @name";
            SqlParameter[] sprs = { new SqlParameter("@name", name) };
            return DBHelper.增删改(sql, sprs, CommandType.Text);
        }
    }
}
